package com.hdw.med.service;

import com.hdw.common.base.BaseException;
import com.hdw.common.db.DynamicDataSource;
import com.hdw.common.db.HikariDataSourceExt;
import com.hdw.common.util.DBUtil;
import com.hdw.common.vo.MetaDataVO;
import com.hdw.med.api.*;
import com.hdw.med.bean.model.BaseDbTpcomp;
import com.hdw.med.bean.model.MdMedPubfld;
import com.hdw.med.bean.model.MdMedTbFld;
import com.hdw.med.bean.vo.TableDDLColumnVO;
import com.hdw.med.bean.vo.TableDDLSqlVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Triple;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;

/**
 * etlTaskOds服务实现类
 * Created by eleven on 2021/11/24.
 */
@Service
@Transactional
public class TableDDLServiceImpl implements TableDDLService {

    private static final Logger LOGGER = LoggerFactory.getLogger(TableDDLServiceImpl.class);

    @Autowired
    BaseDbTpcompService daseDbTpcompService;
    @Autowired
    MdMedTbFldService mdMedTbFldService;
    @Autowired
    MdMedTbService mdMedTbService;
    @Autowired
    MdMedPubfldService mdMedPubfldService;


    /**
     * @param mdMedTbFlds
     * @param dataSourceID
     * @param isAll        List<MdMedTbFld>中是否是全部列，全部列则不在List<MdMedTbFld>中的元数据与对应表的列将删除
     * @return
     * @throws BaseException
     */
    private String commonSaveMdMedTbFldAndSynchroniseTable(List<MdMedTbFld> mdMedTbFlds, String dataSourceID, boolean isAll) throws BaseException {
        if (mdMedTbFlds == null || mdMedTbFlds.size() == 0) return null;
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));

        Map<Integer, BaseDbTpcomp> dbTpcompMap = this.getBaseDbTpcomp(dataSource.getEuTp());

        Long idTb = mdMedTbFlds.get(0).getIdTb();
        String tableName = mdMedTbService.getById(mdMedTbFlds.get(0).getIdTb()).getCd();


        if (StringUtils.isEmpty(tableName)) throw new BaseException("idTb字段不能为空");

        MdMedTbFld mdMedTbFldModel = new MdMedTbFld();
        mdMedTbFldModel.setIdTb(idTb);
        List<MdMedTbFld> oldMdMedTbFlds = mdMedTbFldService.findByModel(mdMedTbFldModel);
        Map<String, MdMedTbFld> oldTbFlds = new LinkedHashMap<>();
        for (MdMedTbFld oldMdMedTbFld : oldMdMedTbFlds) {
            oldTbFlds.put(oldMdMedTbFld.getIdPubfld(), oldMdMedTbFld);
        }

        this.checkAndFillMdMedTbFlds(mdMedTbFlds, dbTpcompMap, oldTbFlds);


        Map<String, MetaDataVO> tableColumnMetaData = null;
        try {
            tableColumnMetaData = DBUtil.getTableColumnMetaDataToMetaDataVO(dataSource, tableName);
        } catch (Exception e) {
        }


        // 表不存在或者md_med_tb_fld没有该表的记录，则创建表
        if (tableColumnMetaData == null || oldMdMedTbFlds == null || oldMdMedTbFlds.size() == 0) {
            //创建表
            String createTableSQL = this.getCreateTableSQL(mdMedTbFlds, dataSource.getEuTp(), tableName, dbTpcompMap);

            if (tableColumnMetaData != null) {
                createTableSQL = String.format("DROP TABLE %s ;", tableName) + createTableSQL;
            }

            this.execTableDDLSQL(createTableSQL, dataSourceID);

            for (MdMedTbFld byModel : oldMdMedTbFlds) {
                mdMedTbFldService.deleteById(byModel.getIdTbFld());
            }
            for (MdMedTbFld mdMedTbFld : mdMedTbFlds) {
                mdMedTbFldService.insert(mdMedTbFld);
            }

            return createTableSQL;
        } else {
            StringBuilder sb = new StringBuilder();

            // 修改表,执行成功一个数据库修改操作，保存一条元数据
            Map<String, MdMedTbFld> newTbFlds = new LinkedHashMap<>();
            for (MdMedTbFld newMdMedTbFld : mdMedTbFlds) {
                newTbFlds.put(newMdMedTbFld.getIdPubfld(), newMdMedTbFld);
            }

            if (isAll) {
                // 删除在新元数据中不存在的老表字段
                for (String oldColumnName : oldTbFlds.keySet()) {
                    MdMedTbFld mdMedTbFld = newTbFlds.get(oldColumnName);
                    if (mdMedTbFld == null) {
                        HashSet<String> oneDropSet = new HashSet<>();
                        oneDropSet.add(oldColumnName.toUpperCase());
                        StringBuilder dropColumnSql = this.genDropAndADDModifySQL(null, oneDropSet, dataSource.getEuTp(), tableName);
                        sb.append(dropColumnSql);
                        this.execTableDDLSQL(dropColumnSql.toString(), dataSourceID);

                        MdMedTbFld mdMedTbFld1 = oldTbFlds.get(oldColumnName);
                        mdMedTbFldService.deleteById(mdMedTbFld1.getIdTbFld());
                    }
                }
            }

            // 新增/修改列
            for (String newColumnName : newTbFlds.keySet()) {
                MdMedTbFld oldTbFld = oldTbFlds.get(newColumnName);
                MdMedTbFld newTbFld = newTbFlds.get(newColumnName);
                Integer operationType = decideColumnOperationType(oldTbFld, newTbFld, dbTpcompMap);

                // 1不修改 2修改 3删除再创建 4创建
                if (operationType == 2) {
                    String columnDDL = this.generateTableColumnDDL(newColumnName.toUpperCase(), newTbFld.getEuJavatp(), dataSource.getEuTp(), dbTpcompMap, newTbFld.getNumLth().intValue(), newTbFld.getNumPrec().intValue());
                    HashSet<String> oneAddSet = new HashSet<>();
                    oneAddSet.add(columnDDL);

                    StringBuilder columnSql = this.genModifySQL(oneAddSet, dataSource.getEuTp(), tableName);
                    sb.append(columnSql);
                    this.execTableDDLSQL(columnSql.toString(), dataSourceID);

                    newTbFld.setIdTbFld(oldTbFld.getIdTbFld());
                    mdMedTbFldService.updateById(newTbFld);
                } else if (operationType == 3) {
                    String columnDDL = this.generateTableColumnDDL(newColumnName.toUpperCase(), newTbFld.getEuJavatp(), dataSource.getEuTp(), dbTpcompMap, newTbFld.getNumLth().intValue(), newTbFld.getNumPrec().intValue());

                    HashSet<String> oneDropSet = new HashSet<>();
                    oneDropSet.add(newColumnName.toUpperCase());
                    HashSet<String> oneAddSet = new HashSet<>();
                    oneAddSet.add(columnDDL);
                    StringBuilder columnSql = this.genDropAndADDModifySQL(oneAddSet, oneDropSet, dataSource.getEuTp(), tableName);
                    sb.append(columnSql);
                    this.execTableDDLSQL(columnSql.toString(), dataSourceID);

                    newTbFld.setIdTbFld(oldTbFld.getIdTbFld());
                    mdMedTbFldService.updateById(newTbFld);
                } else if (operationType == 4) {
                    String columnDDL = this.generateTableColumnDDL(newColumnName.toUpperCase(), newTbFld.getEuJavatp(), dataSource.getEuTp(), dbTpcompMap, newTbFld.getNumLth().intValue(), newTbFld.getNumPrec().intValue());
                    HashSet<String> oneAddSet = new HashSet<>();
                    oneAddSet.add(columnDDL);
                    StringBuilder addColumnSql = this.genDropAndADDModifySQL(oneAddSet, null, dataSource.getEuTp(), tableName);
                    sb.append(addColumnSql);
                    this.execTableDDLSQL(addColumnSql.toString(), dataSourceID);
                    mdMedTbFldService.insert(newTbFld);
                }
            }
            return sb.toString();
        }
    }

    private String getCreateTableSQL(List<MdMedTbFld> mdMedTbFlds, String euTp, String idTb, Map<Integer, BaseDbTpcomp> columnTypes) {
        List<String> columnDDLs = new ArrayList<>();
        for (MdMedTbFld mdMedTbFld : mdMedTbFlds) {
            String columnName = mdMedTbFld.getIdPubfld().toUpperCase();
            String columnDDL = generateTableColumnDDL(columnName, mdMedTbFld.getEuJavatp(), euTp, columnTypes, mdMedTbFld.getNumLth().intValue(), mdMedTbFld.getNumPrec().intValue());
            columnDDLs.add(columnDDL);
        }

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(idTb).append("( ").append(StringUtils.join(columnDDLs, " , ")).append(" ) ");

        return sb.toString();
    }

    private void checkAndFillMdMedTbFlds(List<MdMedTbFld> mdMedTbFlds, Map<Integer, BaseDbTpcomp> dbTpcompMap, Map<String, MdMedTbFld> oldTbFlds) throws BaseException {
        List<MdMedPubfld> pubflds = mdMedPubfldService.findByModel(new MdMedPubfld());
        Map<String, MdMedPubfld> pubfldMap = new HashMap<>();
        for (MdMedPubfld mdMedPubfld : pubflds) pubfldMap.put(mdMedPubfld.getIdPubfld(), mdMedPubfld);


        for (MdMedTbFld mdMedTbFld : mdMedTbFlds) {
            Long idTb = mdMedTbFld.getIdTb();
            if (idTb == null) throw new BaseException("idTb字段不能为空");
            String idPubfld = mdMedTbFld.getIdPubfld();

            //校验idPubfld
            if (StringUtils.isEmpty(idPubfld)) throw new BaseException("idPubfld字段不能为空");
            MdMedPubfld pubfld = pubfldMap.get(idPubfld);
            MdMedTbFld oldTbFld = oldTbFlds.get(idPubfld);
            if (pubfld == null) throw new BaseException(String.format("公共字段[%s]不存在，请配置!", idPubfld));

            if (StringUtils.isEmpty(mdMedTbFld.getNaPubfld())) mdMedTbFld.setNaPubfld(pubfld.getNa());

            //校验EuJavatp
            if (mdMedTbFld.getEuJavatp() == null) mdMedTbFld.setEuJavatp(pubfld.getEuJavatp());
            BaseDbTpcomp baseDbTpcomp = dbTpcompMap.get(mdMedTbFld.getEuJavatp());
            if (baseDbTpcomp == null)
                throw new BaseException(String.format("数据库标准类型[%s]不存在，请检查!", mdMedTbFld.getEuJavatp()));
            //使用JAVA标准类型名称
            mdMedTbFld.setNaJavatp(baseDbTpcomp.getNaJavatp());

            //没有指定则使用元数据配置中的，还没有公共字段中的，再没有则置0
            if (mdMedTbFld.getNumLth() == null && oldTbFld != null) mdMedTbFld.setNumLth(oldTbFld.getNumLth());
            if (mdMedTbFld.getNumLth() == null) mdMedTbFld.setNumLth(pubfld.getNumLth());
            if (mdMedTbFld.getNumLth() == null) mdMedTbFld.setNumLth(0);
            //没有指定则使用公共字段中的，还没有公共字段中的，再没有则置0
            if (mdMedTbFld.getNumPrec() == null && oldTbFld != null) mdMedTbFld.setNumPrec(oldTbFld.getNumPrec());
            if (mdMedTbFld.getNumPrec() == null) mdMedTbFld.setNumPrec(pubfld.getNumPrec());
            if (mdMedTbFld.getNumPrec() == null) mdMedTbFld.setNumPrec(0);
        }
    }

    private Integer decideColumnOperationType(MdMedTbFld oldTbFld, MdMedTbFld newTbFld, Map<Integer, BaseDbTpcomp> dbTpcompMap) {
        BaseDbTpcomp baseDbTpcomp = dbTpcompMap.get(newTbFld.getEuJavatp());
        // 1不修改 2修改 3删除再创建 4创建
        if (oldTbFld == null)
            return 4;
        if (newTbFld.getEuJavatp().equals(oldTbFld.getEuJavatp()) && baseDbTpcomp.getEuUsePrecision() == 0)
            return 1;
        if (newTbFld.getEuJavatp().equals(oldTbFld.getEuJavatp()) && newTbFld.getNumLth() == oldTbFld.getNumLth() && newTbFld.getNumPrec() == oldTbFld.getNumPrec())
            return 1;
        if (!newTbFld.getEuJavatp().equals(oldTbFld.getEuJavatp()) || newTbFld.getNumLth() < oldTbFld.getNumLth() || newTbFld.getNumPrec() < oldTbFld.getNumPrec())
            return 3;
        if (newTbFld.getEuJavatp().equals(oldTbFld.getEuJavatp()) && newTbFld.getNumLth() > oldTbFld.getNumLth() && newTbFld.getNumPrec() >= oldTbFld.getNumPrec())
            return 2;
        if (newTbFld.getEuJavatp().equals(oldTbFld.getEuJavatp()) && newTbFld.getNumLth() >= oldTbFld.getNumLth() && newTbFld.getNumPrec() > oldTbFld.getNumPrec())
            return 2;
        return 1;
    }

    @Override
    public String saveMdMedTbFldAndSynchroniseTable(List<MdMedTbFld> mdMedTbFlds, String dataSourceID) throws BaseException {
        return this.commonSaveMdMedTbFldAndSynchroniseTable(mdMedTbFlds, dataSourceID, true);
    }

    @Override
    public String saveMdMedTbFldAndSynchroniseTableOfDW(List<MdMedTbFld> mdMedTbFlds) throws BaseException {
        return this.saveMdMedTbFldAndSynchroniseTable(mdMedTbFlds, DBUtil.DB_DW);
    }

    @Override
    public String updateMdMedTbFldAndSynchroniseTable(List<MdMedTbFld> mdMedTbFlds, String dataSourceID) throws BaseException {
        return this.commonSaveMdMedTbFldAndSynchroniseTable(mdMedTbFlds, dataSourceID, false);
    }

    @Override
    public String updateMdMedTbFldAndSynchroniseTableOfDW(List<MdMedTbFld> mdMedTbFlds) throws BaseException {
        return this.updateMdMedTbFldAndSynchroniseTable(mdMedTbFlds, DBUtil.DB_DW);
    }


    @Override
    public String deleteMdMedTbFldAndSynchroniseTable(List<MdMedTbFld> mdMedTbFlds, String dataSourceID) throws BaseException {
        if (mdMedTbFlds == null || mdMedTbFlds.size() == 0) return null;
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));

        StringBuilder sb = new StringBuilder();

        for (MdMedTbFld mdMedTbFld : mdMedTbFlds) {
            if (mdMedTbFld.getIdTbFld() != null) {
                MdMedTbFld byId = mdMedTbFldService.getById(mdMedTbFld.getIdTbFld());
                if (byId != null) {
                    HashSet<String> oneDropSet = new HashSet<>();
                    oneDropSet.add(byId.getIdPubfld().toUpperCase());
                    StringBuilder dropColumnSql = this.genDropAndADDModifySQL(null, oneDropSet, dataSource.getEuTp(), byId.getIdTb().toString());
                    sb.append(dropColumnSql);
                    this.execTableDDLSQL(dropColumnSql.toString(), dataSourceID);
                    mdMedTbFldService.deleteById(mdMedTbFld.getIdTbFld());
                }
            } else if (!StringUtils.isEmpty(mdMedTbFld.getIdTb().toString()) && !StringUtils.isEmpty(mdMedTbFld.getIdPubfld())) {
                MdMedTbFld mdMedTbFldModel = new MdMedTbFld();
                mdMedTbFldModel.setIdTb(mdMedTbFld.getIdTb());
                mdMedTbFldModel.setIdPubfld(mdMedTbFld.getIdPubfld());
                List<MdMedTbFld> oldMdMedTbFlds = mdMedTbFldService.findByModel(mdMedTbFldModel);
                if (oldMdMedTbFlds != null && oldMdMedTbFlds.size() > 0) {

                    HashSet<String> oneDropSet = new HashSet<>();
                    oneDropSet.add(oldMdMedTbFlds.get(0).getIdPubfld().toUpperCase());
                    StringBuilder dropColumnSql = this.genDropAndADDModifySQL(null, oneDropSet, dataSource.getEuTp(), oldMdMedTbFlds.get(0).getIdTb().toString());
                    sb.append(dropColumnSql);
                    this.execTableDDLSQL(dropColumnSql.toString(), dataSourceID);

                    for (MdMedTbFld oldMdMedTbFld : oldMdMedTbFlds) {
                        mdMedTbFldService.deleteById(oldMdMedTbFld.getIdTbFld());
                    }
                }
            }
        }
        return sb.toString();
    }

    @Override
    public String deleteMdMedTbFldAndSynchroniseTableOfDW(List<MdMedTbFld> mdMedTbFlds) throws BaseException {
        return this.deleteMdMedTbFldAndSynchroniseTable(mdMedTbFlds, DBUtil.DB_DW);
    }

    @Override
    public TableDDLSqlVO generateCopyTableDDLSQLOfDW(String sourTable, String tarTable) throws BaseException {
        TableDDLSqlVO andModiTableSQL = this.generateTableDDLSQL("select * from " + sourTable, DBUtil.DB_DW, tarTable, DBUtil.DB_DW);
        return andModiTableSQL;
    }

    @Override
    public TableDDLSqlVO generateTableDDLSQLOfDW(String querySQL, String tarTable) throws BaseException {
        TableDDLSqlVO andModiTableSQL = this.generateTableDDLSQL(querySQL, DBUtil.DB_DW, tarTable, DBUtil.DB_DW);
        return andModiTableSQL;
    }

    @Override
    public List<TableDDLColumnVO> generateTableDDLColumnListOfDW(String querySQL, String tarTable) throws BaseException {
        List<TableDDLColumnVO> tableDDLColumnVOS = this.generateTableDDLColumnList(querySQL, DBUtil.DB_DW, tarTable, DBUtil.DB_DW);
        return tableDDLColumnVOS;
    }

    @Override
    public List<TableDDLColumnVO> generateTableDDLColumnList(String querySQL, String sourDataSourceID, String tarTable, String tarDataSourceID) throws BaseException {

        HikariDataSourceExt tarDataSource = DynamicDataSource.getHikariDataSourceById(tarDataSourceID);
        if (tarDataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", tarDataSourceID));
        HikariDataSourceExt sourDataSource = DynamicDataSource.getHikariDataSourceById(sourDataSourceID);
        if (sourDataSource == null) throw new BaseException(String.format("来源获取数据库[%s]失败", sourDataSourceID));

        String tarEuTp = tarDataSource.getEuTp();

        // 列类型字典
        Map<Integer, BaseDbTpcomp> columnTypes = this.getBaseDbTpcomp(tarEuTp);


        Map<String, MetaDataVO> queryColumnMetaData = null;
        Map<String, MetaDataVO> tarTableColumnMetaData = null;
        try {
            queryColumnMetaData = DBUtil.getColumnMetaDataToMetaDataVO(sourDataSource, querySQL);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException(String.format("查询SQL[%s]执行失败,原因：%s", querySQL, e.getMessage()));
        }
        try {
            tarTableColumnMetaData = DBUtil.getTableColumnMetaDataToMetaDataVO(tarDataSource, tarTable);
        } catch (Exception e) {
        }


        List<TableDDLColumnVO> columns = new ArrayList<>();

        for (String queryColumnName : queryColumnMetaData.keySet()) {
            MetaDataVO queryColumn = queryColumnMetaData.get(queryColumnName);
            MetaDataVO tarColumn = null;
            if (tarTableColumnMetaData != null) {
                tarColumn = tarTableColumnMetaData.get(queryColumnName);
            }

            TableDDLColumnVO column = new TableDDLColumnVO(queryColumn, tarColumn);
            columns.add(column);
            if (tarColumn == null) {
                //新增列
                column.setOpType(TableDDLColumnVO.OP_TYPE_ADD);
            } else if (columnIsModify(queryColumn, tarColumn)) {
                column.setOpType(TableDDLColumnVO.OP_TYPE_MODIFY);
            }

            if (tarColumn != null) {
                column.setOldColumnTypeName(columnTypes.get(column.getOldColumnType()).getNaJavatp());
            }
            column.setNewColumnTypeName(columnTypes.get(column.getNewColumnType()).getNaJavatp());
            if (column.getNewColumnScale() < 0) {
                column.setNewColumnScale(0);
            }
        }
        if (tarTableColumnMetaData != null) {
            for (String tarColumnName : tarTableColumnMetaData.keySet()) {
                MetaDataVO tarColumn = tarTableColumnMetaData.get(tarColumnName);
                MetaDataVO queryColumn = queryColumnMetaData.get(tarColumnName);
                if (queryColumn == null) {
                    TableDDLColumnVO column = new TableDDLColumnVO(null, tarColumn);
                    column.setOpType(TableDDLColumnVO.OP_TYPE_DROP);
                    columns.add(column);
                }
            }
        }

        return columns;
    }

    private boolean columnIsModify(MetaDataVO queryColumn, MetaDataVO tarColumn) {
        // 类型不一致（2 8 类型排除）
        if (!tarColumn.getColumnType().equals(queryColumn.getColumnType()) && (tarColumn.getColumnType() != 8 && queryColumn.getColumnType() != 2))
            return true;
        // 老表字段长度 小于 新表
        if (tarColumn.getColumnPrecision() < tarColumn.getColumnPrecision())
            return true;

        return false;
    }

    //--------SQL---------------------

    @Override
    public TableDDLSqlVO generateTableDDLSQL(String querySQL, String sourDataSourceID, String tarTable, String tarDataSourceID) throws BaseException {

        HikariDataSourceExt tarDataSource = DynamicDataSource.getHikariDataSourceById(tarDataSourceID);
        if (tarDataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", tarDataSourceID));
        HikariDataSourceExt sourDataSource = DynamicDataSource.getHikariDataSourceById(sourDataSourceID);
        if (sourDataSource == null) throw new BaseException(String.format("来源获取数据库[%s]失败", sourDataSourceID));

        String tarEuTp = tarDataSource.getEuTp();


        Map<Integer, BaseDbTpcomp> columnTypes = this.getBaseDbTpcomp(tarEuTp);

        TableDDLSqlVO createTableDDLVO = new TableDDLSqlVO();

        Triple<List<String>, List<Integer>, List<String>> queryColumnMetaData = DBUtil.getColumnMetaData(sourDataSource, querySQL);
        Map<String, Triple<String, Integer, String>> tarTableColumnMetaDataToMap = null;
        try {
            tarTableColumnMetaDataToMap = DBUtil.getColumnMetaDataToMap(tarDataSource, tarTable);
        } catch (Exception e) {
            e.printStackTrace();
        }

        String createSQL = this.getCreateTableSQL(queryColumnMetaData, tarEuTp, tarTable, columnTypes);
        String modifySQL = null;

        if (tarTableColumnMetaDataToMap != null) {
            modifySQL = this.getModifyTableSQL(queryColumnMetaData, tarTableColumnMetaDataToMap, tarEuTp, tarTable, columnTypes);
            createSQL = String.format("DROP TABLE %s ;", tarTable) + createSQL;
        }

        createTableDDLVO.setCreateSQL(createSQL);
        createTableDDLVO.setModifySQL(modifySQL);

        return createTableDDLVO;
    }


    private String getModifyTableSQL(Triple<List<String>, List<Integer>, List<String>> queryColumnMetaData, Map<String, Triple<String, Integer, String>> tarTableColumnMetaDataToMap,
                                     String euTp, String idTb, Map<Integer, BaseDbTpcomp> columnTypes) throws BaseException {

        List<String> left = queryColumnMetaData.getLeft();
        List<Integer> middle = queryColumnMetaData.getMiddle();


        Set<String> dropColumn = new HashSet<>();
        Set<String> addColumn = new HashSet<>();
        for (int i = 0; i < left.size(); i++) {
            String columnName = left.get(i);
            Triple<String, Integer, String> oldTableColumn = tarTableColumnMetaDataToMap.get(columnName);
            if (oldTableColumn == null) {
                //新增列
                String columnDDL = this.generateTableColumnDDL(columnName, middle.get(i), euTp, columnTypes);
                addColumn.add(columnDDL);
            } else if (!oldTableColumn.getMiddle().equals(middle.get(i)) && (oldTableColumn.getMiddle() != 8 && middle.get(i) != 2)) {
                //修改列
                String columnDDL = this.generateTableColumnDDL(columnName, middle.get(i), euTp, columnTypes);
                addColumn.add(columnDDL);
                dropColumn.add(columnName);
            }
        }

        for (String oldTableColumnName : tarTableColumnMetaDataToMap.keySet()) {
            if (!left.contains(oldTableColumnName)) {
                //删除列
                dropColumn.add(oldTableColumnName);
            }
        }
        StringBuilder sb = this.genDropAndADDModifySQL(addColumn, dropColumn, euTp, idTb);


        return sb.toString();
    }

    private String getCreateTableSQL(Triple<List<String>, List<Integer>, List<String>> queryColumnMetaData,
                                     String euTp, String idTb, Map<Integer, BaseDbTpcomp> columnTypes) {

        List<String> left = queryColumnMetaData.getLeft();
        List<Integer> middle = queryColumnMetaData.getMiddle();


        List<String> columnDDLs = new ArrayList<>();
        for (int i = 0; i < left.size(); i++) {
            String columnName = left.get(i);
            String columnDDL = generateTableColumnDDL(columnName, middle.get(i), euTp, columnTypes);
            columnDDLs.add(columnDDL);
        }

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE ").append(idTb).append("( ").append(StringUtils.join(columnDDLs, " , ")).append(" ) ");

        return sb.toString();
    }

    private String generateTableColumnDDL(String columnName, Integer columnType, String euTp, Map<Integer, BaseDbTpcomp> columnTypes) {
        BaseDbTpcomp baseDbTpcomp = columnTypes.get(columnType);
        if (baseDbTpcomp == null)
            new BaseException(String.format("获取数据库[%s]类型,euJavatp:[%s]的配置失败,请检查base_db_tpcomp。", euTp, columnType));

        StringBuilder sd = new StringBuilder();
        String euFldtp = baseDbTpcomp.getEuFldtp();
        sd.append(columnName).append(" ").append(euFldtp);
        // base_db_tpcomp 修改 取消精度，老代码注释
        /*if (baseDbTpcomp.getNumLength() != null && baseDbTpcomp.getFgDef() != null) {
            sd.append("(").append(baseDbTpcomp.getNumLength()).append(",").append(Integer.valueOf(baseDbTpcomp.getFgDef())).append(")");
        } else if (baseDbTpcomp.getNumLength() != null && baseDbTpcomp.getFgDef() == null) {
            sd.append("(").append(baseDbTpcomp.getNumLength()).append(")");
        }*/

        return sd.toString();
    }

    private String generateTableColumnDDL(String columnName, Integer columnType, String euTp, Map<Integer, BaseDbTpcomp> columnTypes, Integer precision, Integer scale) {
        BaseDbTpcomp baseDbTpcomp = columnTypes.get(columnType);
        if (baseDbTpcomp == null)
            new BaseException(String.format("获取数据库[%s]类型,euJavatp:[%s]的配置失败,请检查base_db_tpcomp。", euTp, columnType));

        StringBuilder sd = new StringBuilder();
        String euFldtp = baseDbTpcomp.getEuFldtp();
        sd.append(columnName).append(" ").append(euFldtp);
        if (precision != null && precision > 0 && scale != null && scale > 0 && baseDbTpcomp.getEuUsePrecision() == 1) {
            sd.append("(").append(precision).append(",").append(scale).append(")");
        } else if (precision != null && precision > 0 && (scale == null || scale <= 0) && baseDbTpcomp.getEuUsePrecision() == 1) {
            sd.append("(").append(precision).append(")");
        }
        return sd.toString();
    }


    @Override
    public void execTableDDLSQLOfDW(String sql) throws BaseException {
        this.execTableDDLSQL(sql, DBUtil.DB_DW);
    }

    @Override
    public void execTableDDLSQL(String sqls, String dataSourceID) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);

            List<String> sqlList = new ArrayList<>();
            for (String sql : sqls.split(";")) {
                if (!org.springframework.util.StringUtils.isEmpty(sql) && sql.trim().length() > 6) {
                    sqlList.add(sql);
                }
            }
            Statement statement = connection.createStatement();

            for (String beforeSql : sqlList) {
                statement.execute(beforeSql);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException("执行SQL错误!SQL语句为"+sqls+"/n错误为:" + e.getMessage());
        } finally {
            DBUtil.closeDBResources(null, preparedStatement, connection);
        }
    }

    @Override
    public void execTableDDLColumnList(List<TableDDLColumnVO> createTableDDLVO, String tbTar, String dataSourceID) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));
        String euTp = dataSource.getEuTp();
        Map<String, MetaDataVO> tarTableColumnMetaData = null;
        try {
            tarTableColumnMetaData = DBUtil.getTableColumnMetaDataToMetaDataVO(dataSource, tbTar);
        } catch (Exception e) {
        }
        Map<Integer, BaseDbTpcomp> columnTypes = this.getBaseDbTpcomp(euTp);

        Set<String> dropColumn = new LinkedHashSet<>();
        Set<String> addColumn = new LinkedHashSet<>();

        for (TableDDLColumnVO createTableColumnsVO : createTableDDLVO) {
            String opType = createTableColumnsVO.getOpType();
            if (TableDDLColumnVO.OP_TYPE_ADD.equals(opType)) {
                String columnDDL = this.generateTableColumnDDL(createTableColumnsVO.getNewColumnName(), createTableColumnsVO.getNewColumnType(), euTp
                        , columnTypes, createTableColumnsVO.getNewColumnPrecision(), createTableColumnsVO.getNewColumnScale());
                addColumn.add(columnDDL);
            } else if (TableDDLColumnVO.OP_TYPE_DROP.equals(opType)) {
                dropColumn.add(createTableColumnsVO.getNewColumnName());
                dropColumn.add(createTableColumnsVO.getOldColumnName());
            } else if (TableDDLColumnVO.OP_TYPE_MODIFY.equals(opType)) {
                dropColumn.add(createTableColumnsVO.getNewColumnName());
                addColumn.add(createTableColumnsVO.getNewColumnName());
            }
        }
        StringBuilder sb = null;
        if (tarTableColumnMetaData == null) {
            sb = new StringBuilder();
            sb.append("CREATE TABLE ").append(tbTar).append("( ").append(StringUtils.join(addColumn, " , ")).append(" ) ");
        } else {
            sb = this.genDropAndADDModifySQL(addColumn, dropColumn, euTp, tbTar);
        }
        this.execTableDDLSQLOfDW(sb.toString());
    }

    @Override
    public void execTableDDLColumnListOfDW(List<TableDDLColumnVO> createTableDDLVO, String tbTar) throws BaseException {
        this.execTableDDLColumnList(createTableDDLVO, tbTar, DBUtil.DB_DW);
    }


    private StringBuilder genModifySQL(Set<String> columnDDLs, String euTp, String tbTar) throws BaseException {
        StringBuilder sb = new StringBuilder();
        if (DBUtil.DB_TYPE_MYSQL.equals(euTp)) {
            sb.append("ALTER TABLE ").append(tbTar);
            if (columnDDLs != null && columnDDLs.size() > 0) {
                List<String> join = new ArrayList<>();
                for (String columnDDL : columnDDLs) {
                    join.add(String.format(" MODIFY COLUMN %s ", columnDDL));
                }
                sb.append(StringUtils.join(join, ",")).append(";");
            }
        } else if (DBUtil.DB_TYPE_SQLSERVER.equals(euTp)) {
            if (columnDDLs != null && columnDDLs.size() > 0) {
                for (String columnDDL : columnDDLs) {
                    sb.append(String.format("ALTER TABLE %s ALTER COLUMN %s;", tbTar, columnDDL));
                }
            }
        } else if (DBUtil.DB_TYPE_ORACLE.equals(euTp)) {
            sb.append("ALTER TABLE ").append(tbTar);
            if (columnDDLs != null && columnDDLs.size() > 0) {
                List<String> join = new ArrayList<>();
                for (String columnDDL : columnDDLs) {
                    join.add(String.format(" MODIFY (%s) ", columnDDL));
                }
                sb.append(StringUtils.join(join, " ")).append(";");
            }
        } else {
            throw new BaseException(String.format("数据库类型错误[%s]", euTp));
        }

        return sb;
    }

    private StringBuilder genDropAndADDModifySQL(Set<String> addColumn, Set<String> dropColumn, String euTp, String tbTar) throws BaseException {
        if (addColumn == null) addColumn = new HashSet<>();
        if (dropColumn == null) dropColumn = new HashSet<>();

        StringBuilder sb = new StringBuilder();
        if (DBUtil.DB_TYPE_MYSQL.equals(euTp)) {
            if (dropColumn.size() > 0 || addColumn.size() > 0) {
                sb.append("ALTER TABLE ").append(tbTar);
            }
            if (dropColumn.size() > 0) {
                sb.append(" DROP COLUMN ").append(StringUtils.join(dropColumn, " , DROP COLUMN  "));
            }
            if (addColumn.size() > 0) {
                if (dropColumn.size() > 0) sb.append(" , ");
                sb.append(" ADD COLUMN ").append(StringUtils.join(addColumn, " , ADD COLUMN  "));
            }
            if (dropColumn.size() > 0 || addColumn.size() > 0) {
                sb.append(";");
            }
        } else if (DBUtil.DB_TYPE_SQLSERVER.equals(euTp)) {
            if (dropColumn.size() > 0) {
                for (String s : dropColumn) {
                    sb.append(" ALTER TABLE  ").append(tbTar).append(" DROP COLUMN ").append(s).append("; ");
                }
            }
            if (addColumn.size() > 0) {
                for (String s : addColumn) {
                    sb.append(" ALTER TABLE  ").append(tbTar).append(" ADD ").append(s).append("; ");
                }
            }
        } else if (DBUtil.DB_TYPE_ORACLE.equals(euTp)) {
            if (dropColumn.size() > 0) {
                sb.append(" ALTER TABLE  ").append(tbTar).append(" DROP (").append(StringUtils.join(dropColumn, ",")).append("); ");
            }
            if (addColumn.size() > 0) {
                sb.append(" ALTER TABLE  ").append(tbTar);
                for (String s : addColumn) {
                    sb.append(" ADD ").append("(").append(s).append(") ");
                }
                sb.append(";");
            }
        } else {
            throw new BaseException(String.format("数据库类型错误[%s]", euTp));
        }

        return sb;
    }

    /**
     * euJavatp BaseDbTpcomp
     *
     * @return
     */
    private Map<Integer, BaseDbTpcomp> getBaseDbTpcomp(String euTp) throws BaseException {
        BaseDbTpcomp baseDbTpcompModel = new BaseDbTpcomp();
        baseDbTpcompModel.setEuDbtp(euTp);
        List<BaseDbTpcomp> baseDbTpcomps = daseDbTpcompService.findByModel(baseDbTpcompModel);

        if (baseDbTpcomps == null || baseDbTpcomps.size() == 0)
            throw new BaseException(String.format("获取数据库[%s]类型建表配置失败,请检查base_db_tpcomp。", euTp));


        Map<Integer, BaseDbTpcomp> columnTypes = new HashMap<>();
        for (BaseDbTpcomp baseDbTpcomp : baseDbTpcomps) {
            Integer euJavatp = baseDbTpcomp.getEuJavatp();
            columnTypes.put(euJavatp, baseDbTpcomp);
        }
        return columnTypes;
    }
}
